this project represents our inaugural analysis of four companies within the commercial sector over a three-year period, from 2021 to 2023, segmented by quarters. the database utilized in this analysis is entirely hypothetical and does not reflect actual figures or pertain to any real-world company.
through comprehensive data analysis, we have extracted key performance indicators (kpis) for each of the four companies. based on these kpis, we have formulated conclusions and developed strategic recommendations for our organization.
scenario: I am a financial analyst at Tech Company, which operates in a commercial market comprising four companies. Our company has been experiencing a decline in both product sales and profits over time, unlike our competitors who are enjoying high profitability.
->note: If you have any confusing just read print function in the at the Code it will be highly enough.
below is the original hypothetical database used in this analysis:
library(opencpu)## Loading config from C:/Users/A.Dawod/AppData/Local/R/win-library/4.4/opencpu/config/defaults.conf## Loading config from C:\Users\A.Dawod\AppData\Roaming/R/config/R/opencpu/user.conf## Welcome to OpenCPU!library(readxl)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(tidyr)
income_statment <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 1)
balance_sheet_Assets <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 2)
balance_sheet_L_S <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 3)
excel_data<-bind_rows(income_statment,balance_sheet_Assets,balance_sheet_L_S)
### Display the data
print(income_statment)## # A tibble: 48 × 12
## Company Quarter Year Revenue COGS Gross_Profit Operating_Expenses EBIT
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 648721. 365391. 283331. 189537. 93793.
## 2 Tech Q2 2021 565250. 260978. 304271. 127755. 176516.
## 3 Tech Q3 2021 758622. 454160. 304462. 172230. 132231.
## 4 Tech Q4 2021 669067. 350268. 318798. 143990. 174809.
## 5 Tech Q1 2022 881818. 429210. 452608. 256685. 195924.
## 6 Tech Q2 2022 518744. 278455. 240289. 121972. 118317.
## 7 Tech Q3 2022 656602. 344829. 311773. 133764. 178009.
## 8 Tech Q4 2022 922800. 489665. 433135. 250773. 182361.
## 9 Tech Q1 2023 639024. 348730. 290295. 178966. 111329.
## 10 Tech Q2 2023 595526. 336003. 259523. 150406. 109117.
## # ℹ 38 more rows
## # ℹ 4 more variables: Interest_Expense <dbl>, EBT <dbl>, Tax_Expense <dbl>,
## # Net_Income <dbl>print(balance_sheet_Assets)## # A tibble: 48 × 9
## Company Quarter Year Cash Accounts_Receivable Inventory Current_Assets
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 108280. 67301. 60333. 235914.
## 2 Tech Q2 2021 74653. 57080. 36565. 168298.
## 3 Tech Q3 2021 59313. 84926. 59168. 203407.
## 4 Tech Q4 2021 121790. 108084. 66411. 296284.
## 5 Tech Q1 2022 55973. 113650. 48630. 218253.
## 6 Tech Q2 2022 80702. 102817. 33238. 216757.
## 7 Tech Q3 2022 88388. 127710. 61840. 277938.
## 8 Tech Q4 2022 67934. 129025. 86907. 283866.
## 9 Tech Q1 2023 72952. 67319. 69029. 209300.
## 10 Tech Q2 2023 132511. 92360. 46537. 271407.
## # ℹ 38 more rows
## # ℹ 2 more variables: Property_Plant_Equipment <dbl>, Total_Assets <dbl>print(balance_sheet_L_S)## # A tibble: 48 × 12
## Company Quarter Year Accounts_Payable Short_Term_Debt Current_Liabilities
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 64310. 82956. 147266.
## 2 Tech Q2 2021 34109. 64510. 98619.
## 3 Tech Q3 2021 52609. 114660. 167269.
## 4 Tech Q4 2021 47387. 107439. 154827.
## 5 Tech Q1 2022 47422. 130525. 177948.
## 6 Tech Q2 2022 51469. 70189. 121658.
## 7 Tech Q3 2022 68962. 93160. 162122.
## 8 Tech Q4 2022 92628. 98568. 191196.
## 9 Tech Q1 2023 56606. 90380. 146987.
## 10 Tech Q2 2023 43997. 82808. 126805.
## # ℹ 38 more rows
## # ℹ 6 more variables: Long_Term_Debt <dbl>, Total_Liabilities <dbl>,
## # Common_Stock <dbl>, Retained_Earnings <dbl>, Shareholders_Equity <dbl>,
## # Total_Liabilities_and_Equity <dbl>subsequently, i transformed the original database to enhance its readability and clarity. This transformation includes organizing the data in a structured format, making it easier to interpret and analyze. here’s how the data now looks:
Tech <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 1)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Speed <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 2)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Maclr <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 3)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Dain <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 4)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`print(Tech)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 648721 565250 758622 669067 881818 5187… 6566… 9228… 6390…
## 4 COGS 365391 260978 454160 350268 429210 2784… 3448… 4896… 3487…
## 5 Gross_Profit 283331 304271 304462 318798 452608 2402… 3117… 4331… 2902…
## 6 Operating_Expenses 189537 127755 172230 143990 256685 1219… 1337… 2507… 1789…
## 7 EBIT 93793 176516 132231 174809 195924 1183… 1780… 1823… 1113…
## 8 Interest_Expense 1851 4469 11493 3853 5038 8259 9580 6491 4776
## 9 EBT 91943 172047 120738 170956 190885 1100… 1684… 1758… 1065…
## 10 Tax_Expense 27583 51614 36222 51287 57266 33017 50529 52761 31966
## # ℹ 23 more rows
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>print(Speed)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 657220 980669 551447 787047 641032 5633… 9946… 6580… 5749…
## 4 COGS 333620 576060 314556 347393 308822 3100… 5021… 3449… 3306…
## 5 Gross_Profit 283331 404609 236891 439655 332209 2532… 4925… 3130… 2442…
## 6 Operating_Expenses 186260 127755 163765 234290 171591 1565… 2866… 1428… 1323…
## 7 EBIT 93793 276854 73126 205365 160618 96688 2058… 1702… 1119…
## 8 Interest_Expense 7072 4469 12872 6518 3458 9235 7179 13524 2566
## 9 EBT 130267 158890 60254 198846 157160 87454 1987… 1567… 1093…
## 10 Tax_Expense 39080 47667 18076 59654 47148 26236 59616 47029 32806
## # ℹ 23 more rows
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>print(Maclr)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 575230 588116 830948 610934 621120 8448… 5733… 8495… 8109…
## 4 COGS 309856 331783 495346 327718 297236 4590… 3068… 3521… 4031…
## 5 Gross_Profit 283331 256333 335602 283216 323884 3858… 2665… 4974… 4077…
## 6 Operating_Expenses 155277 127755 231040 174450 173750 2269… 1537… 2185… 1947…
## 7 EBIT 93793 128578 104562 108765 150134 1589… 1127… 2788… 2130…
## 8 Interest_Expense 9862 4469 5103 4117 6447 13040 6570 7506 7960
## 9 EBT 100234 117683 99459 104649 143687 1458… 1061… 2713… 2050…
## 10 Tax_Expense 30070 35305 29838 31395 43106 43761 31845 81409 61521
## # ℹ 23 more rows
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>print(Dain)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 582244 600186 950352 970666 512620 5620… 6903… 8925… 7864…
## 4 COGS 316333 334554 556525 471660 214573 2685… 4022… 5346… 3462…
## 5 Gross_Profit 283331 265632 393827 499005 298047 2934… 2881… 3578… 4402…
## 6 Operating_Expenses 174002 127755 272291 243956 145761 1369… 1470… 2096… 2197…
## 7 EBIT 93793 137877 121536 255049 152286 1565… 1410… 1482… 2204…
## 8 Interest_Expense 9789 4469 4735 19283 2081 8831 9876 7790 6859
## 9 EBT 82120 139069 116801 235767 150205 1477… 1311… 1404… 2136…
## 10 Tax_Expense 24636 41721 35040 70730 45061 44312 39358 42136 64083
## # ℹ 23 more rows
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>after refining and adjusting the values, we are now ready to proceed with the analysis.
in the file provided below, you will find three sheets:
DATA <- read_excel("D:/Project/Measures.xlsx", sheet = 1)
print(DATA)## # A tibble: 48 × 27
## Company Year `Current Ratio` `Quick Ratio` `Debt-To-equity` `Debt-To-Assets`
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 TECH 21-Q1 1.60 1.19 0.657 0.359
## 2 TECH 21-Q2 1.71 1.34 0.423 0.282
## 3 TECH 21-Q3 1.22 0.862 0.465 0.299
## 4 TECH 21-Q4 1.91 1.48 0.838 0.426
## 5 TECH 22-Q1 1.23 0.953 0.666 0.379
## 6 TECH 22-Q2 1.78 1.51 1.30 0.511
## 7 TECH 22-Q3 1.71 1.33 0.561 0.332
## 8 TECH 22-Q4 1.48 1.03 0.853 0.414
## 9 TECH 23-Q1 1.42 0.954 0.938 0.448
## 10 TECH 23-Q2 2.14 1.77 0.503 0.314
## # ℹ 38 more rows
## # ℹ 21 more variables: `Debt-To-Captial` <dbl>, `Interest-Coverage` <dbl>,
## # `Recivables Turnover` <dbl>, DSO <dbl>, `Payables Turnover` <dbl>,
## # DPO <dbl>, `Inventory Turnover` <dbl>, DOH <dbl>,
## # `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>,
## # `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## # `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>, …Changes <- read_excel("D:/Project/Measures.xlsx", sheet = 2)
print(Changes)## # A tibble: 48 × 27
## Company `CHANGES %` `Current Ratio` `Quick Ratio` `Debt-To-equity`
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 Tech 21-Q1 0 0 0
## 2 Tech 21-Q2/21-Q1 0.0653 0.120 -0.356
## 3 Tech 21-Q3/21-Q2 -0.287 -0.354 0.0990
## 4 Tech 21-Q4/21-Q3 0.574 0.722 0.804
## 5 Tech 22-Q1/21-Q4 -0.359 -0.358 -0.205
## 6 Tech 22-Q2/22-Q1 0.453 0.583 0.955
## 7 Tech 22-Q3/22-Q2 -0.0378 -0.116 -0.569
## 8 Tech 22-Q4/22-Q3 -0.134 -0.227 0.519
## 9 Tech 23-Q1/22-Q4 -0.0409 -0.0736 0.100
## 10 Tech 23-Q2/23-Q1 0.503 0.858 -0.463
## # ℹ 38 more rows
## # ℹ 22 more variables: `Debt-To-Assets` <dbl>, `Debt-To-Captial` <dbl>,
## # `Interest-Coverage` <dbl>, `Recivables Turnover` <dbl>, DSO <dbl>,
## # `Payables Turnover` <dbl>, DPO <dbl>, `Inventory Turnover` <dbl>,
## # DOH <dbl>, `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>,
## # `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## # `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>, …which contain four main measures
first: financial_health measures
library(ggplot2)
library(plotly)##
## Attaching package: 'plotly'## The following object is masked from 'package:ggplot2':
##
## last_plot## The following object is masked from 'package:stats':
##
## filter## The following object is masked from 'package:graphics':
##
## layoutlibrary(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
company_tech_gross <- c(44, 54, 40, 48, 51, 46, 47, 47, 45, 44, 55, 40)
company_speed_gross <- c(43, 41, 43, 56, 52, 45, 50, 48, 42, 58, 57, 56)
company_maclr_gross <- c(49, 44, 40, 46, 52, 46, 46, 59, 50, 60, 45, 52)
company_dain_gross <- c(49, 44, 41, 51, 58, 52, 42, 40, 56, 50, 51, 54)
company_tech_ebit <- c(14, 31, 17, 26, 22, 23, 27, 20, 17, 18, 31, 11)
company_speed_ebit <- c(14, 28, 13, 26, 25, 17, 21, 26, 19, 38, 33, 33)
company_maclr_ebit <- c(16, 22, 13, 18, 24, 19, 20, 33, 26, 38, 16, 30)
company_dain_ebit <- c(16, 23, 13, 26, 30, 28, 20, 17, 28, 29, 29, 29)
company_tech_net <- c(10, 21, 11, 18, 15, 15, 18, 13, 12, 13, 21, 7)
company_speed_net <- c(14, 11, 8, 18, 17, 11, 14, 17, 13, 26, 22, 22)
company_maclr_net <- c(12, 14, 8, 12, 16, 12, 13, 22, 18, 26, 11, 19)
company_dain_net <- c(10, 16, 9, 17, 21, 18, 13, 11, 19, 20, 20, 19)
data_gross <- data.frame(
Quarter = quarters,
Tech = company_tech_gross,
Speed = company_speed_gross,
Maclr = company_maclr_gross,
Dain = company_dain_gross,
Measure = "Gross_Profit_Margin"
)
data_ebit <- data.frame(
Quarter = quarters,
Tech = company_tech_ebit,
Speed = company_speed_ebit,
Maclr = company_maclr_ebit,
Dain = company_dain_ebit,
Measure = "EBIT_Margin"
)
data_net <- data.frame(
Quarter = quarters,
Tech = company_tech_net,
Speed = company_speed_net,
Maclr = company_maclr_net,
Dain = company_dain_net,
Measure = "Net_Profit_Margin"
)
data <- rbind(data_gross, data_ebit, data_net)
# Use pivot_longer to reshape the data
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for Gross Profit Margin
data_gross_filtered <- data %>% filter(Measure == "Gross_Profit_Margin")
# Plot Gross Profit Margin
p_gross <- ggplot(data_gross_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Gross Profit Margin Comparison",
x = "Year-Quarter",
y = "Gross Profit Margin (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_gross)# Filter data for EBIT Margin
data_ebit_filtered <- data %>% filter(Measure == "EBIT_Margin")
# Plot EBIT Margin
p_ebit <- ggplot(data_ebit_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "EBIT Margin Comparison",
x = "Year-Quarter",
y = "EBIT Margin (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_ebit)# Filter data for Net Profit Margin
data_net_filtered <- data %>% filter(Measure == "Net_Profit_Margin")
# Plot Net Profit Margin
p_net <- ggplot(data_net_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Net Profit Margin Comparison",
x = "Year-Quarter",
y = "Net Profit Margin (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_net)# Load required packages
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
company_tech_roa <- c(9, 15, 10, 33, 19, 11, 13, 27, 10, 8, 20, 16)
company_speed_roa <- c(10, 13, 5, 29, 15, 7, 16, 26, 10, 21, 26, 45)
company_maclr_roa <- c(12, 16, 10, 11, 17, 19, 10, 27, 27, 16, 7, 17)
company_dain_roa <- c(11, 16, 9, 17, 17, 14, 12, 10, 17, 20, 18, 19)
company_tech_roe <- c(17, 27, 14, 32, 26, 37, 22, 27, 20, 17, 25, 25)
company_speed_roe <- c(15, 19, 12, 24, 31, 13, 42, 23, 17, 60, 31, 47)
company_maclr_roe <- c(24, 44, 27, 19, 34, 54, 29, 48, 80, 24, 11, 35)
company_dain_roe <- c(28, 30, 20, 29, 28, 22, 21, 21, 27, 51, 33, 43)
company_tech_oroa <- c(28, 26, 16, 21, 24, 17, 25, 20, 13, 15, 29, 9)
company_speed_oroa <- c(20, 27, 9, 26, 19, 13, 24, 20, 14, 35, 35, 23)
company_maclr_oroa <- c(32, 23, 17, 15, 23, 28, 18, 39, 34, 30, 9, 26)
company_dain_oroa <- c(36, 24, 16, 27, 19, 23, 18, 17, 24, 29, 25, 30)
data_roa <- data.frame(
Quarter = quarters,
Tech = company_tech_roa,
Speed = company_speed_roa,
Maclr = company_maclr_roa,
Dain = company_dain_roa,
Measure = "ROA"
)
data_roe <- data.frame(
Quarter = quarters,
Tech = company_tech_roe,
Speed = company_speed_roe,
Maclr = company_maclr_roe,
Dain = company_dain_roe,
Measure = "ROE"
)
data_oroa <- data.frame(
Quarter = quarters,
Tech = company_tech_oroa,
Speed = company_speed_oroa,
Maclr = company_maclr_oroa,
Dain = company_dain_oroa,
Measure = "Operating_ROA"
)
data <- rbind(data_roa, data_roe, data_oroa)
# Use pivot_longer to reshape the data
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for ROA
data_roa_filtered <- data %>% filter(Measure == "ROA")
# Plot ROA
p_roa <- ggplot(data_roa_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "ROA Comparison",
x = "Year-Quarter",
y = "ROA (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_roa)# Filter data for ROE
data_roe_filtered <- data %>% filter(Measure == "ROE")
# Plot ROE
p_roe <- ggplot(data_roe_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "ROE Comparison",
x = "Year-Quarter",
y = "ROE (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_roe)# Filter data for Operating ROA
data_oroa_filtered <- data %>% filter(Measure == "Operating_ROA")
# Plot Operating ROA
p_oroa <- ggplot(data_oroa_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Operating ROA Comparison",
x = "Year-Quarter",
y = "Operating ROA (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_oroa)second: solvency measures
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
company_tech_debt_to_assets <- c(36, 28, 30, 43, 38, 51, 33, 41, 45, 31, 33, 59)
company_speed_debt_to_assets <- c(26, 40, 33, 34, 43, 37, 52, 37, 37, 51, 31, 47)
company_maclr_debt_to_assets <- c(43, 56, 56, 35, 43, 56, 56, 35, 57, 27, 31, 43)
company_dain_debt_to_assets <- c(50, 40, 46, 37, 35, 35, 36, 44, 29, 53, 39, 50)
company_tech_debt_to_equity <- c(66, 42, 46, 84, 67, 130, 56, 85, 94, 50, 53, 191)
company_speed_debt_to_equity <- c(38, 77, 56, 58, 83, 64, 138, 64, 66, 119, 50, 99)
company_maclr_debt_to_equity <- c(86, 156, 158, 62, 86, 156, 158, 62, 173, 42, 49, 90)
company_dain_debt_to_equity <- c(126, 76, 103, 64, 58, 58, 62, 92, 46, 136, 72, 110)
company_tech_debt_to_capital <- c(40, 30, 32, 46, 40, 57, 36, 46, 48, 33, 34, 66)
company_speed_debt_to_capital <- c(27, 43, 36, 37, 45, 39, 58, 39, 40, 54, 33, 50)
company_maclr_debt_to_capital <- c(46, 61, 61, 38, 46, 61, 61, 38, 63, 30, 33, 47)
company_dain_debt_to_capital <- c(56, 43, 51, 39, 37, 37, 38, 48, 31, 58, 42, 52)
data_debt_to_assets <- data.frame(
Quarter = quarters,
Tech = company_tech_debt_to_assets,
Speed = company_speed_debt_to_assets,
Maclr = company_maclr_debt_to_assets,
Dain = company_dain_debt_to_assets,
Measure = "Debt_To_Assets"
)
data_debt_to_equity <- data.frame(
Quarter = quarters,
Tech = company_tech_debt_to_equity,
Speed = company_speed_debt_to_equity,
Maclr = company_maclr_debt_to_equity,
Dain = company_dain_debt_to_equity,
Measure = "Debt_To_Equity"
)
data_debt_to_capital <- data.frame(
Quarter = quarters,
Tech = company_tech_debt_to_capital,
Speed = company_speed_debt_to_capital,
Maclr = company_maclr_debt_to_capital,
Dain = company_dain_debt_to_capital,
Measure = "Debt_To_Capital"
)
data <- bind_rows(data_debt_to_assets, data_debt_to_equity, data_debt_to_capital)
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for Debt-To-Assets
data_debt_to_assets_filtered <- data %>% filter(Measure == "Debt_To_Assets")
# Plot Debt-To-Assets
p_debt_to_assets <- ggplot(data_debt_to_assets_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Debt-To-Assets Comparison",
x = "Year-Quarter",
y = "Debt-To-Assets (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_debt_to_assets)# Filter data for Debt-To-Equity
data_debt_to_equity_filtered <- data %>% filter(Measure == "Debt_To_Equity")
# Plot Debt-To-Equity
p_debt_to_equity <- ggplot(data_debt_to_equity_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Debt-To-Equity Comparison",
x = "Year-Quarter",
y = "Debt-To-Equity (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_debt_to_equity)# Filter data for Debt-To-Capital
data_debt_to_capital_filtered <- data %>% filter(Measure == "Debt_To_Capital")
# Plot Debt-To-Capital
p_debt_to_capital <- ggplot(data_debt_to_capital_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Debt-To-Capital Comparison",
x = "Year-Quarter",
y = "Debt-To-Capital (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_debt_to_capital)third: activity measures
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
# Data for each measure
company_tech_receivable <- c(19.28, 9.09, 10.68, 7.02, 5.89, 8.58, 4.06, 5.09, 13.71, 6.92, 3.64, 10.73)
company_speed_receivable <- c(10.52, 6.64, 4.39, 4.82, 12.17, 7.92, 4.39, 8.04, 7.16, 5.41, 6.24, 11.57)
company_maclr_receivable <- c(18.78, 6.94, 7.54, 8.55, 9.97, 5.74, 7.53, 5.90, 7.43, 6.24, 8.84, 3.66)
company_dain_receivable <- c(14.71, 6.55, 7.75, 7.32, 13.50, 5.61, 6.42, 6.54, 6.22, 5.62, 7.21, 4.86)
company_tech_payable <- c(11.36, 5.30, 10.47, 7.01, 9.05, 5.63, 5.73, 7.80, 12.32, 15.27, 7.33, 6.88)
company_speed_payable <- c(10.30, 8.03, 5.02, 6.35, 6.10, 7.59, 7.27, 4.50, 11.34, 12.63, 7.23, 5.08)
company_maclr_payable <- c(18.05, 8.65, 9.21, 5.30, 6.40, 11.97, 5.71, 6.08, 15.92, 6.01, 4.62, 6.31)
company_dain_payable <- c(11.10, 6.96, 8.95, 6.96, 5.47, 9.59, 11.25, 9.87, 10.72, 11.89, 5.98, 9.08)
company_tech_inventory <- c(12.11, 5.39, 9.49, 5.58, 7.46, 6.80, 7.25, 6.58, 4.47, 5.81, 8.76, 6.85)
company_speed_inventory <- c(11.73, 7.30, 4.24, 6.86, 5.87, 6.11, 8.66, 6.43, 7.04, 6.18, 8.61, 6.05)
company_maclr_inventory <- c(14.01, 6.02, 6.14, 4.54, 6.37, 8.32, 3.80, 4.88, 9.64, 5.99, 5.24, 6.41)
company_dain_inventory <- c(10.17, 5.76, 7.14, 5.06, 3.64, 7.58, 7.69, 7.59, 6.17, 10.79, 8.19, 7.74)
company_tech_cash <- c(16.95, 39.09, 37.78, 65.33, 70.60, 31.40, 76.44, 80.36, 78.61, 91.63, 92.15, 34.22)
company_speed_cash <- c(30.39, 59.54, 96.43, 71.40, 32.30, 57.68, 75.05, 21.08, 70.64, 97.63, 50.35, 20.02)
company_maclr_cash <- c(25.27, 71.06, 68.26, 54.23, 36.85, 76.96, 80.50, 76.71, 64.05, 58.65, 32.01, 98.85)
company_dain_cash <- c(27.80, 66.60, 57.39, 69.60, 60.59, 75.15, 71.85, 66.98, 83.79, 68.07, 34.10, 82.05)
data_receivable <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_receivable, company_speed_receivable, company_maclr_receivable, company_dain_receivable),
Measure = 'Receivable Turnover'
)
data_payable <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_payable, company_speed_payable, company_maclr_payable, company_dain_payable),
Measure = 'Payable Turnover'
)
data_inventory <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_inventory, company_speed_inventory, company_maclr_inventory, company_dain_inventory),
Measure = 'Inventory Turnover'
)
data_cash <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_cash, company_speed_cash, company_maclr_cash, company_dain_cash),
Measure = 'Cash Conversion Cycle'
)
data <- bind_rows(data_receivable, data_payable, data_inventory, data_cash)
# Filter data for Receivable Turnover
data_receivable_filtered <- data %>% filter(Measure == "Receivable Turnover")
# Plot Receivable Turnover
p_receivable <- ggplot(data_receivable_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Receivable Turnover Comparison",
x = "Year-Quarter",
y = "Receivable Turnover",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_receivable)# Filter data for Payable Turnover
data_payable_filtered <- data %>% filter(Measure == "Payable Turnover")
# Plot Payable Turnover
p_payable <- ggplot(data_payable_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Payable Turnover Comparison",
x = "Year-Quarter",
y = "Payable Turnover",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_payable)# Filter data for Inventory Turnover
data_inventory_filtered <- data %>% filter(Measure == "Inventory Turnover")
# Plot Inventory Turnover
p_inventory <- ggplot(data_inventory_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Inventory Turnover Comparison",
x = "Year-Quarter",
y = "Inventory Turnover",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_inventory)# Filter data for Cash Conversion Cycle
data_cash_filtered <- data %>% filter(Measure == "Cash Conversion Cycle")
# Plot Cash Conversion Cycle
p_cash <- ggplot(data_cash_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Cash Conversion Cycle Comparison",
x = "Year-Quarter",
y = "Cash Conversion Cycle",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_cash)finally: liquidity measures
library(ggplot2)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
# Current Ratio data
current_ratio_tech <- c(1.60, 1.71, 1.22, 1.91, 1.23, 1.78, 1.71, 1.48, 1.42, 2.14, 1.69, 1.16)
current_ratio_speed <- c(2.12, 1.20, 1.87, 1.49, 1.31, 1.82, 1.00, 1.93, 1.87, 1.39, 1.17, 1.69)
current_ratio_maclr <- c(2.37, 1.61, 1.68, 1.31, 2.37, 1.61, 1.68, 1.31, 1.23, 1.48, 2.40, 1.59)
current_ratio_dain <- c(1.26, 2.63, 1.63, 1.80, 1.65, 1.73, 2.24, 1.07, 1.72, 1.02, 1.24, 1.82)
# Quick Ratio data
quick_ratio_tech <- c(1.19, 1.34, 0.86, 1.48, 0.95, 1.51, 1.33, 1.03, 0.95, 1.77, 1.46, 0.71)
quick_ratio_speed <- c(1.71, 0.83, 1.51, 1.14, 0.99, 1.47, 0.77, 1.62, 1.48, 1.12, 0.89, 1.49)
quick_ratio_maclr <- c(1.90, 1.14, 1.13, 1.03, 1.90, 1.14, 1.13, 1.03, 1.03, 1.19, 1.83, 1.28)
quick_ratio_dain <- c(0.90, 2.12, 1.12, 1.26, 1.39, 1.43, 1.71, 0.79, 1.47, 0.82, 1.02, 1.50)
data_current <- data.frame(
Quarter = rep(quarters, 4),
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(current_ratio_tech, current_ratio_speed, current_ratio_maclr, current_ratio_dain),
Measure = 'Current Ratio'
)
data_quick <- data.frame(
Quarter = rep(quarters, 4),
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(quick_ratio_tech, quick_ratio_speed, quick_ratio_maclr, quick_ratio_dain),
Measure = 'Quick Ratio'
)
data <- bind_rows(data_current, data_quick)
# Filter data for Current Ratio
data_current_filtered <- data %>% filter(Measure == "Current Ratio")
# Plot Current Ratio
p_current <- ggplot(data_current_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Current Ratio Comparison",
x = "Year-Quarter",
y = "Current Ratio",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_current)# Filter data for Quick Ratio
data_quick_filtered <- data %>% filter(Measure == "Quick Ratio")
# Plot Quick Ratio
p_quick <- ggplot(data_quick_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Quick Ratio Comparison",
x = "Year-Quarter",
y = "Quick Ratio",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_quick)speed: upon reviewing its data, it is evident that this company is the dominant player in the commercial sector. this dominance has allowed it to impose strict controls on accounts receivable, enabling the company to collect its funds promptly at the end of each quarter (“for example, if a customer purchases on account for $100,000 at the beginning of q1 2021, they have until the end of q1 2022 to settle the amount due”). this practice contributes to fluctuations in the cash conversion cycle each year. regarding the company’s debt, the solvency ratios indicate that the company leverages its market power and dominance to increase its borrowing capacity. this has significantly impacted the net profit margin, as the higher level of debt has effectively reduced the company’s tax burden. in terms of profitability, the key indicators, roe (return on equity) and oroa (operating return on operating assets), show positive performance for two reasons. firstly, oroa remains strong because the company has maintained stable inventory turnover (meaning the period in which the inventory is quickly sold). secondly, roe has remained stable due to the improvement and consistency in the dupont analysis.
maclr: similar to the previous company, maclr has comparable margin rates, which is expected for all companies in this sector due to the above-average economic conditions and demand. in contrast to the previous company, maclr does not impose any restrictions on accounts receivables. this is evident from the low receivable turnover over the years, which has significantly impacted the cash conversion cycle by extending it. this extension is detrimental to any commercial company aiming to shorten the cycle for quick cash collection and inventory purchases. consequently, this has also negatively affected the activity ratios, showing a decline over the years. regarding solvency, despite the cash flow issue, the company’s net profit margin remains stable and does not decline. this is because the company heavily relies on external debt rather than operating cash flow and liquidity. although this strategy has reduced taxes and increased net income, it places the company at high risk of bankruptcy if it fails to meet its debt obligations. this issue is further highlighted by the declining profitability ratios over the coming years, confirming the problem of reduced operating efficiency. additionally, the declining inventory turnover over the years indicates that the company is struggling with its operational systems.
dain: regarding margins, there are no issues, and the indicators are normal, similar to other companies in the same sector. however, dain faces a problem similar to maclr, as it does not appear to impose any restrictions on accounts receivables. the consequences of this have already been discussed in the case of maclr. one significant difference is that dain has a high inventory turnover, indicating it sells its products quickly, almost surpassing the dominant company, speed. this efficiency has also helped dain reduce its cash conversion cycle. additionally, dain has managed to extend its payable turnover without adversely affecting its operations over the years. therefore, its only issue lies in the receivable turnover. regarding debt, dain is similar to maclr, except for the bankruptcy risk. dain has sufficient liquidity from its operating activities to manage its debt obligations, especially short-term debts.
tech: unlike the other companies, tech shows a decrease in margin ratios, which is unusual compared to the surrounding companies. another issue is the lack of restrictions on accounts receivables, despite stable inventory turnover rates and an increase in payable turnover. the lack of controls on accounts receivables has significantly extended the cash conversion cycle due to the decrease in receivable turnover ratio. this situation poses a greater risk to the company, particularly if bad debt and the allowance for doubtful accounts increase, which could severely weaken its liquidity ratios. although the company does not rely on debt to the same extent as other companies, this will affect the stability of its operating systems. the roa indicator is stable but tends to decline in the coming years. the oroa has decreased significantly due to the company’s deteriorating ability to increase sales and another reason that will be mentioned later. now, addressing the crucial point, the company has declining margins despite the surrounding companies not experiencing similar issues. if we look at the gross profit margin, it is lower compared to other companies. this could be due to one of two reasons: either the company is not well-known and thus sells less, which is unlikely since the data shows that all four companies have nearly equal profits, or the second and more likely reason is that tech’s product pricing is higher than that of other companies. upon investigating, it was found that tech has higher costs of goods sold compared to the other companies. regarding the ebit margin (earnings before interest and tax), it has been low over the years, also due to higher operating expenses than the other companies. ultimately, this has resulted in a lower net profit margin. additionally, the lower debt levels compared to other companies have led to higher taxes for tech compared to its peers.
->now that we have reviewed each company, summarizing their advantages and disadvantages, let’s discuss the solutions that should be presented to tech, in order to make it competitive with speed and other companies.
cost analysis and value engineering: the first step tech should take is to thoroughly examine its costs by implementing value engineering techniques. this involves identifying and eliminating all non-value-added activities to reduce overall costs. by doing so, the company can lower its product prices, thereby increasing its gross profit margin and enhancing its inventory turnover.
implementing appropriate controls on accounts receivables: after reducing prices, the company should introduce suitable controls on accounts receivables that align with its market position. this strategy will help increase the receivable turnover, thereby shortening the cash conversion cycle. consequently, this will improve liquidity and boost operating activities.
optimizing debt-to-equity ratio: once the company stabilizes and its market value increases, it can leverage the third crucial factor: optimizing its debt-to-equity ratio. by aligning its debt levels with its financial position and liquidity at that time, the company can increase its net profit margin and benefit from a reduction in taxes.
implementing these strategies will enhance tech’s competitiveness with speed and other companies and improve its overall financial performance.